-- EXEC sp_load_vendor_sales_addresses 10
CREATE PROC sp_load_vendor_sales_addresses @vendor_Id INT 
AS
BEGIN
	DECLARE @results TABLE(Address_ID int, Address_1 varchar(50), Address_2 varchar(50), City varchar(50), State varchar(50),
		Zipcode varchar(50), Country varchar(50), Phone_Number varchar(50), Fax_Number varchar(50), Email varchar(50),
		Contact_Person varchar(50), Cell_Number varchar(50), Address_3 varchar(50), Address_4 varchar(50), is_Default INT, ContactPersonAddress_Id INT )

	DECLARE @salesAddress_Id INT
	DECLARE @Address_ID INT
	DECLARE @Address_1 VARCHAR(50)
	DECLARE @Address_2 VARCHAR(50)
	DECLARE @City VARCHAR(50)
	DECLARE @State VARCHAR(50)
	DECLARE @Zipcode VARCHAR(50)
	DECLARE @Country VARCHAR(50)
	DECLARE @Phone_Number VARCHAR(50)
	DECLARE @Fax_Number VARCHAR(50)
	DECLARE @Email VARCHAR(50)
	DECLARE @Contact_Person VARCHAR(50)
	DECLARE @Cell_Number VARCHAR(50)
	DECLARE @Address_3 VARCHAR(50)
	DECLARE @Address_4 VARCHAR(50)
	DECLARE @is_Default INT
	DECLARE @contactPersonAddressId INT

	SELECT @salesAddress_Id = Address_Id FROM VENDOR WHERE Vendor_Id = @vendor_Id

	SELECT @Address_1 = Address_1, @Address_2 = Address_2, @City = City, @State = State, @Zipcode = Zipcode, @Country = Country, @Phone_Number = Phone_Number, @Fax_Number = Fax_Number, @Email = Email, @Contact_Person = Contact_Person, @Cell_Number = Cell_Number, @Address_3 = Address_3, @Address_4 = Address_4 FROM Address WHERE Address_Id = @salesAddress_Id

	INSERT INTO @results(Address_ID, Address_1, Address_2, City, State, Zipcode, Country, Phone_Number, Fax_Number, Email, Contact_Person, Cell_Number, Address_3, Address_4, is_Default, ContactPersonAddress_Id)
	VALUES( @salesAddress_Id, @Address_1, @Address_2, @City, @State, @Zipcode, @Country, @Phone_Number, @Fax_Number, @Email, @Contact_Person, @Cell_Number, @Address_3, @Address_4, 0, @salesAddress_Id )
	
	DECLARE ContactPersonCursor CURSOR FOR SELECT Address_Id, VCP_Is_Default FROM Vendor_Contact_Persons WHERE Vendor_ID = @vendor_Id AND VCP_Type = 'Sales' AND VCP_Is_Active = 0
	OPEN ContactPersonCursor
	FETCH NEXT FROM ContactPersonCursor INTO @address_Id, @is_Default
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @Phone_Number = Phone_Number, @Fax_Number = Fax_Number, @Email = Email, @Contact_Person = Contact_Person, @Cell_Number = Cell_Number FROM Address WHERE Address_Id = @address_Id

		IF @is_Default <> 0
		BEGIN
			INSERT INTO @results(Address_ID, Address_1, Address_2, City, State, Zipcode, Country, Phone_Number, Fax_Number, Email, Contact_Person, Cell_Number, Address_3, Address_4, is_Default, ContactPersonAddress_Id)
			VALUES( @salesAddress_Id, @Address_1, @Address_2, @City, @State, @Zipcode, @Country, @Phone_Number, @Fax_Number, @Email, @Contact_Person, @Cell_Number, @Address_3, @Address_4, 1, @address_Id )
		END
		ELSE
		BEGIN
			UPDATE @results SET is_Default = 1 WHERE is_Default = 0

			INSERT INTO @results(Address_ID, Address_1, Address_2, City, State, Zipcode, Country, Phone_Number, Fax_Number, Email, Contact_Person, Cell_Number, Address_3, Address_4, is_Default, ContactPersonAddress_Id)
			VALUES( @salesAddress_Id, @Address_1, @Address_2, @City, @State, @Zipcode, @Country, @Phone_Number, @Fax_Number, @Email, @Contact_Person, @Cell_Number, @Address_3, @Address_4, 0, @address_Id )
		END

		FETCH NEXT FROM ContactPersonCursor INTO @address_Id, @is_Default
	END
	CLOSE ContactPersonCursor
	DEALLOCATE ContactPersonCursor
	
	SELECT * FROM @results
	
END